﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using ConfigLibrary;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;


namespace Contacts
{
    public class contactconn
    {
        MySqlConnection conn = new MySqlConnection();
        MySqlCommand contactscmd = new MySqlCommand();
        MySqlDataAdapter contactsda = new MySqlDataAdapter();
        MySqlCommandBuilder contactsbldr = new MySqlCommandBuilder();
        public DataSet contacts = new DataSet();
        public DataTable table = new DataTable();
        private Config config;
        private string _username;
        private string _host;
        private string _pass;
        private string _database;
        private int _port;
        DateTime today = DateTime.Today;

        public contactconn()
        {
            Stream s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);
            BinaryFormatter b = new BinaryFormatter();
            config = (Config)b.Deserialize(s);
            s.Close();
            config.GetSqlInfo(out _host, out _username, out _pass, out _database, out _port);
        }

        public void connecttridentspock()
        {
            string[] database = _database.Split(new Char[] { ' ', ',' });
            conn.ConnectionString = "Database=" + database[1] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;
            //conn.ConnectionString = "Database=contacts;DataSource=localhost;User Id = root;Password=;
            conn.Open();
            GC.KeepAlive(conn);
        }        
        
        public void connectcontacts()
        {
            string[] database = _database.Split(new Char[] { ' ', ',' });
            conn.ConnectionString = "Database=" + database[0] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;
            conn.Open();
            GC.KeepAlive(conn);            
        }

        public void loadtables(string table)
        {
            if (table == "trident" || table == "spock")
            {
                string active = "Active";
                DateTime offtrident = today.AddDays(-180);
                string removetrident = offtrident.ToString("MM-dd-yyyy");
                string todaystr = today.ToString("MM-dd-yyyy");
                int year = today.Year;
                int pastyear = year - 1;
                string command = "(Select * from " + table + " where rundate = '" + today + "' AND proj_status = '" + active + "' AND prog_yr_prj >= '" + year + 
                "') UNION (Select * from " + table + " where rundate = '" + today + "' AND prog_yr_prj >= '" + pastyear + "' AND proj_status = '" + active + "' AND go_prj >= '" + removetrident +
                "') ORDER BY city";                
                contactscmd.CommandText = command;                
            }
            else 
            {
                contactscmd.CommandText = "Select * from " + table;
            }
            contactscmd.Connection = conn;
            contactsda.SelectCommand = contactscmd;
            contactsda.TableMappings.Add(table, table);            
            contactsda.FillSchema(contacts, SchemaType.Source, table);
            contactsda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            contactsda.Fill(contacts, table);            
            contactscmd.ExecuteNonQuery();            
        }

        public void disconnect()
        {
            conn.Close();
        }    
    }    
}
